# coding=utf-8
"""
Created on 2018年8月9日
"""

from alivedb_base.rdbs.base_db import BaseDB
from util_base.ufo_decorators import db_operator
from common_base.constants import BannerState
from util_base.common_utils import deal_id_list_beta


class AliveCms(BaseDB):
    """
    """

    database = 'alive_cms'

    @db_operator
    def get_userid_by_userkey(self, user_key):
        """
        通过userKey获取userid
        """
        sql = '''select userid from T_USER_KEY where userKey=UNHEX('%s')''' % (user_key)
        result = self.query(sql)
        return result[0]['userid'] if result else 0

    @db_operator
    def save_user_feedback_info(self, userid, content, contact, image_urls, addtime):
        """
        保存用户的反馈信息
        """
        data = dict(
            userid=userid,
            content=content,
            contact=contact,
            image_urls=image_urls,
            addtime=addtime
        )
        return self.save('user_feedback', data)

    @db_operator
    def get_banner_list(self, state=BannerState.ONLINE):
        """
        获取banner列表
        """
        sql = ''' select banner_id,image_url,jump_type,jump_info,`interval`,banner_type,article_id
                  from banner where state=%s order by order_num asc ''' % (state)

        return self.query(sql)

    @db_operator
    def get_for_you_article_list(self, userid, page_size, article_score=None):
        """
        for you 列表页帖子
        """
        sql = ''' select a.articleId,a.articleBody,a.authorId,a.commentCount,a.likeCount,a.shareCount,a.cvCount,
                  a.videoPath_big,a.videoPath_small,a.length,a.width,a.height,a.thumbPath_big,a.thumbPath_small,
                  DATE_FORMAT(a.regDate, '%%Y%%m%%d%%H%%i%%s') AS regDate,a.hlsPath,a.contentState,a.encryptedArticleId 
                  from T_ARTICLE_FORYOU_SCORE as s
                  left join T_ARTICLE as a on a.articleId=s.article_id
                  where a.contentState=1 and a.isHide=0 '''

        if article_score is not None:
            sql += ''' and s.score < %s ''' % (article_score)

        # 拉黑
        sql += ''' and a.authorId not in (SELECT blocker FROM T_USERBLOCK WHERE blocked=%s) ''' % (userid)

        sql += ''' order by s.score desc, a.regDate desc limit %s ''' % (page_size)

        return self.query(sql)

    @db_operator
    def batch_get_users_info(self, target_userid_list, check_doIFollow=False, check_isBlocked=False, userid=0):
        """
        批量获取用户信息
        如果需要判断是否被我关注(check_doIFollow=True) 或 是否被我拉黑(check_isBlocked=True) 逻辑需要传userid
        """
        sql = ''' select obj.objId as userid, LOWER(HEX(obj.refKey)) as userKey, objName as userName, 
                  IF(s.score IS NULL , 0 , s.score) AS userScore, 
                  obj.profileThumbPath_big, obj.profileThumbPath_small '''

        if check_doIFollow and userid:
            sql += ''' ,IF((SELECT COUNT(1) FROM T_FRIEND WHERE primaryObjId=%s AND targetObjId=obj.objId)=0, 0, 1) AS doIFollow ''' % (userid)
        
        if check_isBlocked and userid:
            sql += ''' ,IF((SELECT COUNT(1) FROM T_USERBLOCK WHERE blocker=%s AND blocked=obj.objId)=0, 0, 1) AS isBlocked ''' % (userid)
        
        sql += ''' from T_OBJECT as obj
                   left join T_USER_SCORE as s on obj.objId=s.userid
                   where obj.objId in %s ''' % (deal_id_list_beta(target_userid_list))

        return self.query(sql)

    @db_operator
    def page_get_comments_by_articleids(self, userid, article_ids, page_size):
        """
        获取帖子后N条评论
        """
        if not article_ids:
            return list()

        sql = ''' call get_comments_by_acticleIds(%s, '%s', %s) ''' % (userid, ','.join([str(e) for e in article_ids]), page_size)
        return self.query(sql)

    @db_operator
    def batch_judge_article_like(self, userid, article_ids):
        """
        批量判断用户是否点赞
        """
        if not article_ids:
            return list()

        sql = ''' select articleId,regDate,authorId 
                  from T_ARTICLELIKE
                  where authorId=%s and articleId in %s ''' % (userid, deal_id_list_beta(article_ids))

        return self.query(sql)

    @db_operator
    def get_article_for_you_score_by_articleid(self, article_id):
        """
        获取帖子分数
        """
        sql = ''' select score from T_ARTICLE_FORYOU_SCORE where article_id=%s ''' % (article_id)
        result = self.query(sql)
        return result[0]['score'] if result else 0

    @db_operator
    def get_article_list_by_time(self, start_time, start, page_size):
        """
        通过时间分段获取帖子
        """
        sql = ''' select articleId,commentCount,likeCount,shareCount,cvCount,regDate
                  from T_ARTICLE
                  where contentState=1 and isHide=0 and regDate > '%s'
                  order by articleId asc limit %s,%s ''' % (start_time, start, page_size)

        return self.query(sql)

    @db_operator
    def bach_save_for_you_article_score(self, data):
        """
        批量for you页帖子的分数
        """
        self.batch_save('T_ARTICLE_FORYOU_SCORE', ['article_id', 'score'], data)

    @db_operator
    def delete_for_you_article_score(self):
        """
        删除for you页帖子分数
        """
        self.delete_by_condition('T_ARTICLE_FORYOU_SCORE', dict(top=0))

    @db_operator
    def get_for_you_top_articles(self):
        """
        获取for you 页指定的帖子
        """
        sql = '''select article_id from T_ARTICLE_FORYOU_SCORE where top=1 '''
        return self.query(sql)

    @db_operator
    def get_weekly_hot_articles(self, userid, page_size, order_id=None):
        """
        周最热帖子
        """
        sql = ''' select a.articleId,a.articleBody,a.authorId,a.commentCount,a.likeCount,a.shareCount,a.cvCount,
                  a.videoPath_big,a.videoPath_small,a.length,a.width,a.height,a.thumbPath_big,a.thumbPath_small,
                  DATE_FORMAT(a.regDate, '%%Y%%m%%d%%H%%i%%s') AS regDate,a.hlsPath,a.contentState,a.encryptedArticleId,
                  wa.id as orderId
                  from T_WEEKLYHOTARTICLE as wa
                  left join T_ARTICLE as a on wa.articleId=a.articleId 
                  left join T_OBJECT as o on a.authorId=o.objId  
                  where a.contentState=1 and a.isHide=0 and o.memberType != 3 '''

        if order_id:
            sql += ''' and wa.id < '%s' ''' % (order_id)

        sql += ''' and a.authorId not in (SELECT blocker FROM T_USERBLOCK WHERE blocked=%s) ''' % (userid)

        sql += ''' order by wa.id desc limit %s ''' % (page_size)

        return self.query(sql)

    @db_operator
    def get_articles_by_article_id_list(self, article_id_list):
        """
        根据帖子id列表批量获取帖子详情
        """
        if not article_id_list:
            return list()

        sql = ''' select a.articleId,a.articleBody,a.authorId,a.commentCount,a.likeCount,a.shareCount,a.cvCount,
                  a.videoPath_big,a.videoPath_small,a.length,a.width,a.height,a.thumbPath_big,a.thumbPath_small,
                  DATE_FORMAT(a.regDate, '%%Y%%m%%d%%H%%i%%s') AS regDate,a.hlsPath,a.contentState,a.encryptedArticleId
                  from T_ARTICLE as a
                  where 1=1 '''
        sql += ''' and a.articleId in %s ''' % (deal_id_list_beta(article_id_list))

        return self.query(sql)

    @db_operator
    def get_article_info_by_article_id(self, article_id):
        """
        根据帖子id获取帖子详情
        """
        sql = ''' select a.articleId,a.articleBody,a.authorId,a.commentCount,a.likeCount,a.shareCount,a.cvCount,
                  a.videoPath_big,a.videoPath_small,a.length,a.width,a.height,a.thumbPath_big,a.thumbPath_small,
                  DATE_FORMAT(a.regDate, '%%Y%%m%%d%%H%%i%%s') AS regDate,a.regDate as regTime, a.hlsPath,
                  a.contentState,a.encryptedArticleId
                  from T_ARTICLE as a
                  where 1=1 '''
        sql += ''' and a.articleId=%s ''' % (article_id)
        result = self.query(sql)
        return result[0] if result else dict()

    @db_operator
    def get_keywords_by_active_type(self, active_type):
        """
        获取keyword
        """
        sql = ''' select id as keywordId,keyword,color,`type` 
                  from T_SELECTED_KEYWORD 
                  where ISACTIVATED = %s order by orderNum desc ''' % (active_type)

        return self.query(sql)

    @db_operator
    def get_search_tab_keyword_articles(self, userid, keyword_id_list, page_size):
        """
        获取发现页的keyword articles
        """
        sql = ''' call get_search_tab_keyword_acticles(%s, '%s', %s) ''' \
              % (userid, ','.join([str(e) for e in keyword_id_list]), page_size)

        return self.query(sql)

    @db_operator
    def get_weekly_hot_article_order_id_by_article_id(self, article_id):
        """
        获取周最热帖子的排序id
        """
        sql = ''' select id from T_WEEKLYHOTARTICLE where articleId=%s ''' % (article_id)
        result = self.query(sql)
        return result[0]['id'] if result else ''

    @db_operator
    def count_keyword_articles_num(self, keyword_id_list):
        """
        统计每个keyword下面有多少帖子
        """
        sql = ''' select keywordId, count(1) as total_data from T_SELECTED_KEYWORD_ARTICLE
                  where keywordId in %s
                  group by keywordId ''' % (deal_id_list_beta(keyword_id_list))

        return self.query(sql)

    @db_operator
    def event_manage_daily_batch(self):
        """
        数据库event
        """
        sql = ''' call manage_dailyBatch() '''
        return self.origin_execute(sql)

    @db_operator
    def event_manage_get_prev_best(self):
        """
        数据库event
        """
        sql = ''' call manage_getPrevBest() '''
        return self.origin_execute(sql)

    @db_operator
    def event_manage_recent_count(self):
        """
        数据库event
        """
        count_sql = ''' SELECT COUNT(1) AS v_count FROM T_RECENT_ARTICLE '''
        result = self.query(count_sql)
        if not result:
            return
        v_count = result[0]['v_count']
        if v_count > 500:
            limit_count = v_count - 500
            del_sql = ''' DELETE FROM T_RECENT_ARTICLE ORDER BY articleId ASC LIMIT %s ''' % (limit_count)
            self.origin_execute(del_sql)

    @db_operator
    def event_manage_select_keyword_article(self):
        """
        数据库event
        """
        sql = ''' call manage_selectKeywordArticle() '''
        return self.origin_execute(sql)

    @db_operator
    def event_count_user_score(self):
        """
        数据库event
        """
        del_sql = ''' DELETE FROM T_USER_SCORE '''
        self.origin_execute(del_sql)

        insert_sql = ''' INSERT INTO T_USER_SCORE(USERID , SCORE)
                        (
                          SELECT H.userId , SUM(T.score) AS score 
                          FROM T_AWARD_HISTORY H 
                          INNER JOIN T_AWARD_TYPE T  ON H.awardType=T.type
                          WHERE H.regDate >= DATE_ADD(NOW(), INTERVAL -T.expPeriod HOUR) 
                          group BY H.userId		
                          HAVING score >0 
                        ) '''

        self.origin_execute(insert_sql)

    @db_operator
    def get_blocker_by_condition(self, blocked):
        """
        获取屏蔽着
        """
        sql = ''' select blocker from T_USERBLOCK where blocked=%s ''' % (blocked)
        return self.query(sql)

    @db_operator
    def get_blocked_by_condition(self, blocker):
        """
        获取被屏蔽着
        """
        sql = ''' select blocked from T_USERBLOCK where blocker=%s ''' % (blocker)
        return self.query(sql)

    @db_operator
    def judge_user_already_liked(self, userid, article_id):
        """
        判断用户是否已经点过赞
        """
        sql = ''' SELECT id as likeId, articleId, authorId
                  FROM T_ARTICLELIKE 
                  WHERE articleId=%s AND authorId=%s ''' % (article_id, userid)
        result = self.query(sql)
        return True if result else False

    @db_operator
    def save_like(self, article_id, userid):
        """
        保存点赞数据
        """
        like_data = dict(
            articleId=article_id,
            authorId=userid,
            regDate='now()'
        )
        return self.save('T_ARTICLELIKE', like_data)

    @db_operator
    def save_t_msg(self, sender_id, receiver_id, msg_type, article_id):
        """
        保存站内信信息
        """
        msg_data = dict(
            senderId=sender_id,
            receiverId=receiver_id,
            msgType=msg_type,
            articleId=article_id
        )
        return self.save('T_MSG', msg_data)

    @db_operator
    def get_like_msg_diff_hours(self, userid, article_id):
        """
        获取对于同一个帖子点赞发送站内信，的时间间隔
        """
        sql = ''' SELECT TIMESTAMPDIFF(HOUR , LASTPUSHDATE , v_curDate) as v_hours 
                  FROM T_ARTICLELIKE_HISTORY 
                  WHERE ARTICLEID=%s AND OBJID=%s ''' % (article_id, userid)
        result = self.query(sql)
        return result[0]['v_hours'] if result else None

    @db_operator
    def save_article_like_history(self, userid, article_id):
        """
        保存article like history
        """
        data = dict(
            articleId=article_id,
            objId=userid,
            lastPushDate='now()'
        )
        return self.save('T_ARTICLELIKE_HISTORY', data)

    @db_operator
    def update_article_like_history_lash_push_date(self, userid, article_id):
        """
        更新article like history last push date
        """
        data = dict(
            lashPushDate='now()'
        )
        return self.update_by_condition('T_ARTICLELIKE_HISTORY', data, dict(articleId=article_id, objId=userid))



